﻿using Photocopy.Business;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.ModelBinding;

namespace Photocopy.Model
{
    public class ProductData
    {
        
        PhotocopyDataContext db = new PhotocopyDataContext();
        // Hàm get sản phẩm trang chủ //
        public List<ProductRP> getHotProduct()
        {
            List<ProductRP> list = new List<ProductRP>();
            var query = ((from sp in db.SanPhams
                          join nsx in db.NhaSanXuats on sp.MaSanXuat equals nsx.MaSanXuat
                          where sp.Hot == true
                          orderby sp.LuotXem descending
                          select new
                          {
                              sp.SanPhamID,
                              sp.MaSanPham,
                              sp.TenSanPham,
                              sp.DonGia,
                              sp.Hot,
                              sp.HinhMau,
                              nsx.TenSanXuat
                          }).Take(4)).ToList();
            for(int i=0;i<query.Count();i++)
            {
                ProductRP sp = new ProductRP();
                sp.SanPhamID = query[i].SanPhamID;
                sp.MaSanPham = query[i].MaSanPham;
                sp.TenSanPham = query[i].TenSanPham;
                sp.DonGia = query[i].DonGia;
                sp.Hot = query[i].Hot;
                sp.HinhMau = query[i].HinhMau;
                sp.NhaSanXuat = query[i].TenSanXuat;
                
                list.Add(sp);
            }
            return list;
        }
        public List<ProductRP> getHotBuyProduct()
        {
            List<ProductRP> list = new List<ProductRP>();
            var query = ((from sp in db.SanPhams                          
                          orderby sp.LuotXem descending
                          select sp).Take(4)).ToList();
            for (int i = 0; i < query.Count(); i++)
            {
                ProductRP sp = new ProductRP();
                sp.TenSanPham = query[i].TenSanPham;
                sp.DonGia = query[i].DonGia;
                sp.SanPhamID = query[i].SanPhamID;
                list.Add(sp);
            }
            return list;
        }
        public List<ProductRP> getSaleProduct()
        {
            List<ProductRP> list = new List<ProductRP>();
            var query = ((from sp in db.SanPhams
                          join sale in db.Sales on sp.SanPhamID equals sale.SanPhamID
                          orderby sp.LuotXem descending
                          select new 
                          {
                              sp.SanPhamID,
                              sp.MaSanPham,
                              sp.MaSanXuat,
                              sp.ChucNang,
                              sp.DonGia,
                              sp.Hot,
                              sp.NgayNhapHang,
                              sp.LuotXem,
                              sp.HinhMau,
                              sp.SoLuong,
                              sp.TenSanPham,
                              sale.SaleID,
                              sale.GiaGiam,
                              sale.NgayApDung,
                              sale.NgayHetHan
                          }).Take(4)).ToList();
            for (int i = 0; i < query.Count(); i++)
            {
                ProductRP sp = new ProductRP();
                sp.TenSanPham = query[i].TenSanPham;
                sp.DonGia = query[i].DonGia;
                sp.GiaGiam = query[i].GiaGiam;
                sp.NgayApDung = query[i].NgayApDung;
                sp.NgayHetHan = query[i].NgayHetHan;
                sp.SaleID = query[i].SaleID;
                sp.SanPhamID = query[i].SanPhamID;
                list.Add(sp);
            }
            return list;
        }

        //
        public List<ProductRP> getProduct()
        {
            List<ProductRP> listSP = new List<ProductRP>();
            //listSP = db.SanPhams.ToList();
            var abc = (from sp in db.SanPhams
                       join nsx in db.NhaSanXuats on sp.MaSanXuat equals nsx.MaSanXuat
                       orderby sp.MaSanPham
                       select new
                       {
                           sp.SanPhamID,
                           sp.MaSanPham,
                           sp.MaSanXuat,
                           sp.ChucNang,
                           sp.DonGia,
                           sp.Hot,
                           sp.NgayNhapHang,
                           sp.LuotXem,
                           sp.HinhAnh,
                           sp.HinhMau,
                           sp.SoLuong,
                           sp.TenSanPham,
                           nsx.TenSanXuat
                       }).ToList();
            for (int i = 0; i < abc.Count; i++)
            {
                ProductRP sp = new ProductRP();
                sp.ID = i + 1;
                sp.SanPhamID = abc[i].SanPhamID;
                sp.ChucNang = abc[i].ChucNang;
                sp.DonGia = abc[i].DonGia;
                sp.Hot = abc[i].Hot;
                sp.MaSanPham = abc[i].MaSanPham;
                sp.MaSanXuat = abc[i].MaSanXuat;
                sp.NgayNhapHang = abc[i].NgayNhapHang;
                sp.LuotXem = abc[i].LuotXem;
                sp.HinhAnh = abc[i].HinhAnh;
                sp.HinhMau = abc[i].HinhMau;
                sp.SoLuong = abc[i].SoLuong;
                sp.TenSanPham = abc[i].TenSanPham;
                sp.NhaSanXuat = abc[i].TenSanXuat;
                listSP.Add(sp);
            }
            return listSP;
        }

        //---------------------------------------------------------

        public bool CheckSale(int Sanphamid)
        {
            bool b = true;
            var query = from sp in db.Sales
                        where sp.SanPhamID == Sanphamid
                        select sp;
            int n = query.Count();
            if(n == 0)
            {
                b = false;
            }
            else if(n == 1)
            {
                b = true;
            }
            return b;
        }
        public ProductRP getProduct(int Sanphamid)
        {
            var query = (from sp in db.SanPhams
                         join nsx in db.NhaSanXuats on sp.MaSanXuat equals nsx.MaSanXuat
                         where sp.SanPhamID == Sanphamid
                         select new
                         {
                             sp.SanPhamID,
                             sp.MaSanPham,
                             sp.MaSanXuat,
                             sp.ChucNang,
                             sp.DonGia,
                             sp.Hot,
                             sp.NgayNhapHang,
                             sp.LuotXem,
                             sp.HinhAnh,
                             sp.HinhMau,
                             sp.SoLuong,
                             sp.TenSanPham,
                             nsx.TenSanXuat
                         }).First();
            var checksale = from s in db.Sales
                            where s.SanPhamID == Sanphamid
                            select s;
            int n = checksale.Count();
            ProductRP sp1 = new ProductRP();
            sp1.SanPhamID = query.SanPhamID;
            sp1.ChucNang = query.ChucNang;
            sp1.DonGia = query.DonGia;
            sp1.Hot = query.Hot;
            sp1.MaSanPham = query.MaSanPham;
            sp1.MaSanXuat = query.MaSanXuat;
            sp1.NgayNhapHang = query.NgayNhapHang;
            sp1.LuotXem = query.LuotXem;
            sp1.HinhAnh = query.HinhAnh;
            sp1.HinhMau = query.HinhMau;
            sp1.SoLuong = query.SoLuong;
            sp1.TenSanPham = query.TenSanPham;
            sp1.NhaSanXuat = query.TenSanXuat;
            if (n == 1)
            {
                sp1.GiaGiam = (checksale.First()).GiaGiam;
                sp1.NgayApDung = (checksale.First()).NgayApDung;
                sp1.NgayHetHan = (checksale.First()).NgayHetHan;
            }
            return sp1;

        }
        public ProductRP getProductSale(int Sanphamid)
        {
            var query = (from sp in db.SanPhams
                        join nsx in db.NhaSanXuats on sp.MaSanXuat equals nsx.MaSanXuat
                        join sale in db.Sales on sp.SanPhamID equals sale.SanPhamID
                        where sp.SanPhamID == Sanphamid
                        select new
                        {
                            sp.SanPhamID,
                            sp.MaSanPham,
                            sp.MaSanXuat,
                            sp.ChucNang,
                            sp.DonGia,
                            sp.Hot,
                            sp.NgayNhapHang,
                            sp.LuotXem,
                            sp.HinhAnh,
                            sp.HinhMau,
                            sp.SoLuong,
                            sp.TenSanPham,
                            nsx.TenSanXuat,
                            sale.GiaGiam,
                            sale.NgayApDung,
                            sale.NgayHetHan
                        }).First();
            ProductRP sp1 = new ProductRP();
            sp1.SanPhamID = query.SanPhamID;
            sp1.ChucNang = query.ChucNang;
            sp1.DonGia = query.DonGia;
            sp1.Hot = query.Hot;
            sp1.MaSanPham = query.MaSanPham;
            sp1.MaSanXuat = query.MaSanXuat;
            sp1.NgayNhapHang = query.NgayNhapHang;
            sp1.LuotXem = query.LuotXem;
            sp1.HinhAnh = query.HinhAnh;
            sp1.HinhMau = query.HinhMau;
            sp1.SoLuong = query.SoLuong;
            sp1.TenSanPham = query.TenSanPham;
            sp1.NhaSanXuat = query.TenSanXuat;
            sp1.GiaGiam = query.GiaGiam;
            sp1.NgayApDung = query.NgayApDung;
            sp1.NgayHetHan = query.NgayHetHan;
            return sp1;

        }
        

        //-------------------------------------------------------------

        //Hàm xử lý trang ProductList.aspx //
            //Hàm trả về thông tin sản phẩm , chuyển qua trang ProductList//
        public ProductRP getProductEdit(int Sanphamid)
        {
            var query = from product in db.SanPhams
                        where product.SanPhamID == Sanphamid
                        select product;
            ProductRP pro = new ProductRP();
            foreach(var n in query)
            {
                pro.SanPhamID = n.SanPhamID;
                pro.MaSanPham = n.MaSanPham;
                pro.MaSanXuat = n.MaSanXuat;
                pro.TenSanPham = n.TenSanPham;
                pro.DonGia = n.DonGia;
                pro.SoLuong = n.SoLuong;
                pro.NgayNhapHang = n.NgayNhapHang;
            }
            return pro;
        }
            //Hàm xóa Product//
        public void DeleteProduct(int Sanphamid)
        {
            var query_product = from product in db.SanPhams
                                where product.SanPhamID == Sanphamid
                                select product;
            var query_titleoption = from to in db.MainOptions
                                    where to.SanPhamID == Sanphamid
                                    select to;
            foreach(var m in query_titleoption)
            {
                var query_option = from o in db.Options
                                   where o.MainOptionID == m.MainOptionID
                                   select o;
                foreach(var n in query_option)
                {
                    db.Options.DeleteOnSubmit(n);
                }
                db.MainOptions.DeleteOnSubmit(m);
            }
            foreach(var p in query_product)
            {
                db.SanPhams.DeleteOnSubmit(p);
            }
            db.SubmitChanges();
        }
            //Hàm lấy thông tin sơ lược sản phẩm
        public string GetProfileProduct(int Sanphamid)
        {
            string gioithieu = "";
            var query = from sp in db.SanPhams
                        where sp.SanPhamID == Sanphamid
                        select sp;
            foreach(var i in query)
            {
                gioithieu = i.GioiThieu;
            }
            return gioithieu;
        }

        
        //--------------------------------------------------------------

        public void InsertProduct(ProductRP product)
        {
            SanPham sp = new SanPham();
            sp.MaSanPham = product.MaSanPham;
            sp.TenSanPham = product.TenSanPham;
            sp.MaSanXuat = product.MaSanXuat;
            sp.DonGia = product.DonGia;
            sp.SoLuong = product.SoLuong;
            sp.NgayNhapHang = product.NgayNhapHang;
            db.SanPhams.InsertOnSubmit(sp);
            db.SubmitChanges();
        }
        public int GetSanphamID(string Masanpham,string Masanxuat,string Tensanpham)
        {
            int sanphamid = 0;
            var query = from sp in db.SanPhams
                        where sp.MaSanPham == Masanpham && sp.MaSanXuat == Masanxuat && sp.TenSanPham == Tensanpham
                        select sp;
            foreach(var n in query)
            {
                sanphamid = n.SanPhamID;
            }
            return sanphamid;
        }
        public List<Message> GetMessage(int Sanphamid,int Top)
        {
            List<Message> listms = new List<Message>();
            var query = ((from cmt in db.Comments
                          where cmt.SanPhamID == Sanphamid
                          orderby cmt.ID descending
                          select cmt).Take(Top)).ToList();
            for (int i = 0; i < query.Count; i++)
            {
                Message ms = new Message();
                ms.Name = query[i].Name;
                ms.Content = query[i].Comt;
                ms.Postdate = query[i].Postdate;
                listms.Add(ms);
            }
            return listms;
        }
        public void SaveMessage(int SanphamID,string Name,string Content,DateTime dt)
        {
            Comment cmt = new Comment();
            cmt.Name = Name;
            cmt.Comt = Content;
            cmt.Postdate = dt;
            cmt.SanPhamID = SanphamID;
            db.Comments.InsertOnSubmit(cmt);
            db.SubmitChanges();
        }
        public int GetRowMes(int SanphamID)
        {
            int row = 0;
            var query = from ms in db.Comments
                        where ms.SanPhamID == SanphamID
                        select ms;
            foreach(var n in query)
            {
                row++;
            }
            return row;
        }
        //public void InsertImage(string Masanpham,string Imagename,string Imageurl)
        //{
        //    Image img = new Image();
        //    img.MaSanPham = Masanpham;
        //    img.ImageName = Imagename;
        //    img.ImageURL = Imageurl;
        //    db.Images.InsertOnSubmit(img);
        //    db.SubmitChanges();
        //}
        //public void DeleteImage(string Masanpham,string Imagename)
        //{
        //    var query = from img in db.Images
        //                where img.MaSanPham == Masanpham && img.ImageName == Imagename
        //                select img;
        //    foreach(var q in query)
        //    {
        //        db.Images.DeleteOnSubmit(q);
        //    }
        //    db.SubmitChanges();
        //}
        //public List<Image> GetImage(string Masanpham)
        //{
        //    List<Image> listimg = new List<Image>();
        //    var query = (from img in db.Images
        //                 where img.MaSanPham == Masanpham
        //                 select img).ToList();
        //    for (int i = 0; i < query.Count(); i++)
        //    {
        //        Image image = new Image();
        //        image.ImageName = query[i].ImageName;
        //        image.ImageURL = query[i].ImageURL;
        //        listimg.Add(image);
        //    }
        //    return listimg;
        //}
        public void InsertMainOption(MainOptionList titleoption)
        {
            var query = (from mo in db.MainOptions
                        where mo.MainOptionID == titleoption.MainOptionID && mo.MainOptionName == titleoption.MainOptionName
                        select mo).ToList();
            if (query.Count() == 0)
            {
                MainOption mainoption = new MainOption();
                mainoption.SanPhamID = titleoption.SanPhamID;
                mainoption.MainOptionName = titleoption.MainOptionName;                
                db.MainOptions.InsertOnSubmit(mainoption);
                db.SubmitChanges();
            }
        }
        public void InsertOption(OptionDetail optiondetail)
        {
            Option option = new Option();
            option.MainOptionID = optiondetail.MainOptionID;
            option.OptionName = optiondetail.OptionName;
            option.OptionDescription = optiondetail.OptionDescription;
            db.Options.InsertOnSubmit(option);
            db.SubmitChanges();
        }
        public List<MainOption> GetMainOption(int SanphamID)
        {
            List<MainOption> listmainoption = new List<MainOption>();
            var query = (from maintoption in db.MainOptions
                         where maintoption.SanPhamID == SanphamID
                         select maintoption).ToList();
            for(int i = 0 ;i < query.Count(); i++)
            {
                MainOption mainoption = new MainOption();
                mainoption.MainOptionName = query[i].MainOptionName;
                listmainoption.Add(mainoption);
            }
            return listmainoption;
        }

        //---------------------------------------------------------------------

        //Trang Edit Product //
            //Cập nhập chi tiết sản phẩm//
        public void UpdateProduct(ProductRP product)
        {
            var query = (from sp in db.SanPhams
                         where sp.SanPhamID == product.SanPhamID
                         select sp).First();
            query.TenSanPham = product.TenSanPham;
            query.MaSanPham = product.MaSanPham;
            query.MaSanXuat = product.MaSanXuat;
            query.DonGia = product.DonGia;
            query.SoLuong = product.SoLuong;
            db.SubmitChanges();
        }
            //Thêm sửa xóa Chức năng chính//
        public List<MainOption> GetEditMainOption(int SanphamID)
        {
            var query = from mainoption in db.MainOptions
                        where mainoption.SanPhamID == SanphamID
                        select mainoption;
            List<MainOption> list = new List<MainOption>();
            foreach(var n in query)
            {
                MainOption to = new MainOption();
                to.MainOptionID = n.MainOptionID;
                to.MainOptionName = n.MainOptionName;
                list.Add(to);
            }
            return list;
        }
        public int GetMainOptionID(int SanphamID,string Tenchucnang)
        {
            var query = from n in db.MainOptions
                        where n.SanPhamID == SanphamID && n.MainOptionName == Tenchucnang
                        select n;
            int mainoptionid = 0;
            foreach(var i in query)
            {
                mainoptionid = i.MainOptionID;
            }
            return mainoptionid;
        }
        public void UpdateMainOption(int MainoptionID,string Mainoptionname)
        {
            var mo = (from n in db.MainOptions
                      where n.MainOptionID == MainoptionID
                      select n).First();
            mo.MainOptionName = Mainoptionname;
            db.SubmitChanges();
        }
        public void DeleteMainOption(int MainoptionID)
        {
            var query = (from n in db.MainOptions
                         where n.MainOptionID == MainoptionID
                         select n).First();
            db.MainOptions.DeleteOnSubmit(query);
            db.SubmitChanges();
        }
        public void AddMainOption(MainOptionList mo)
        {
            MainOption m = new MainOption();
            m.SanPhamID = mo.SanPhamID;
            m.MainOptionName = mo.MainOptionName;
            db.MainOptions.InsertOnSubmit(m);
            db.SubmitChanges();
        }
            //Thêm sửa xóa chức năng chi tiết//
        public List<OptionDetail> GetEditOption(int MainoptionID)
        {
            List<OptionDetail> listoption = new List<OptionDetail>();
            var query = (from option in db.Options
                         where option.MainOptionID == MainoptionID
                         select option).ToList();
            for(int i=0 ; i<query.Count() ; i++)
            {
                OptionDetail od = new OptionDetail();
                od.ID = i + 1;
                od.OptionID = query[i].OptionID;
                od.OptionName = query[i].OptionName;
                od.OptionDescription = query[i].OptionDescription;
                listoption.Add(od);
            }
            return listoption;
        }
        public void AddOption(OptionDetail od)
        {
            Option option = new Option();
            option.MainOptionID = od.MainOptionID;
            option.OptionName = od.OptionName;
            option.OptionDescription = od.OptionDescription;
            db.Options.InsertOnSubmit(option);
            db.SubmitChanges();
        }
        public void UpdateOption(OptionDetail od)
        {
            var query = (from n in db.Options
                        where n.OptionID == od.OptionID
                        select n).First();
            query.OptionName = od.OptionName;
            query.OptionDescription = od.OptionDescription;
            db.SubmitChanges();
        }
        public void DeleteOption(int OptionID)
        {
             var option = (from o in db.Options
                                   where o.OptionID == OptionID
                                   select o).First();
             db.Options.DeleteOnSubmit(option);
             db.SubmitChanges();
        }


        //-------------------------------------------------------------------------
    }
}